﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using HotelManagement.DTO;

namespace HotelManagement.DAO
{
    class CustomerDAO:DataProvider
    {
        public CustomerDAO() :
            base()
        {
        }

        public bool Insert(CustomerDTO customer)
        {
            string strInsert = "insert into CUSTOMER(Name,Gender,IDNo,CusTypeID, Birth,PhoneNumber,Address,Occupation,Nationality) values (";
            strInsert += "N'" + customer.Name + "',";
            strInsert += "N'" + customer.Gender + "',";
            strInsert += "'" + customer.IDNo_Passport + "',";
            strInsert += customer.CusTypeID + ",";
            strInsert += "'" + customer.BirthYear + "',";
            strInsert += "'" + customer.PhoneNumber + "',";
            strInsert += "N'" + customer.Address + "',";
            strInsert += "N'" + customer.Occupation + "',";
            strInsert += "N'" + customer.Nationality + "')";

            return base._ExecuteQuery(strInsert);
        }

        public bool Update(CustomerDTO customer)
        {
            string strUpdate = "update customer set ";
            strUpdate += "customer.Name=N'" + customer.Name + "',";
            strUpdate += "customer.Gender=N'" + customer.Gender + "',";
            strUpdate += "customer.IDNo='" + customer.IDNo_Passport + "',";
            strUpdate += "customer.CusTypeID=" + customer.CusTypeID + ",";
            strUpdate += "customer.Birth='" + customer.BirthYear + "',";
            strUpdate += "customer.PhoneNumber ='" + customer.PhoneNumber + "',";
            strUpdate += "customer.Address = N'" + customer.Address + "',";
            strUpdate += "customer.Occupation = N'" + customer.Occupation + "',";
            strUpdate += "customer.Nationality = N'" + customer.Nationality + "'";
            strUpdate += " where customer.CustomerID = '" + customer.CustomerID + "'";

            return base._ExecuteQuery(strUpdate);
        }

        public bool Delete(CustomerDTO customer)
        {
            string strDelete = "delete from customer where customer.CustomerID='" + customer.CustomerID + "'";

            return base._ExecuteQuery(strDelete);
        }

        public DataTable Select()
        {
            string strSelect = "select * from customer";
            
            return base._Select(strSelect);
        }

        public DataTable SelectWithType()
        {
            string strSelect = "SELECT CUSTOMER.CustomerID, CUSTOMER.Name, CUSTOMER.Gender, CUSTOMER.IDNo, CUSTOMERTYPE.TypeName, CUSTOMER.Birth, customer.PhoneNumber,customer.Address,customer.Occupation,customer.Nationality ";
            strSelect += " from CUSTOMER INNER JOIN";
            strSelect += " CUSTOMERTYPE ON CUSTOMER.CusTypeID = CUSTOMERTYPE.CusTypeID";
            return base._Select(strSelect);
        }

        public int SelectLastInsertID()
        {
            return base.SelectLastInsertID("Customer", "CustomerID");
        }
    }
}
